SodClearingRecordV5

V8 Message Definiton

SodClearingRecords contain start-of-day option position and mark details. They can be source directly from a prime broker by SpiderRock or inserted by clients. They can also be modified after the start of trading. These records are the source of the CLR side positions in SR risk records.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	ClientRisk
Product	SRRisk
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''
secKey_at	enum - AssetType	PRI	'None'
secKey_ts	enum - TickerSrc	PRI	'None'
secKey_tk	VARCHAR(12)	PRI	''
secKey_yr	SMALLINT UNSIGNED	PRI	0
secKey_mn	TINYINT UNSIGNED	PRI	0
secKey_dy	TINYINT UNSIGNED	PRI	0
secKey_xx	DOUBLE	PRI	0
secKey_cp	enum - CallPut	PRI	'Call'
secType	enum - SpdrKeyType	PRI	'None'
tradeDate	DATE	PRI	'1900-01-01'	effective tradeDate of this clearing record
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
daySide	enum - DaySide		'StartOfDay'	startofday or prev weekdays endofday ie corpaction adjusted or not
clrPosition	INT		0	clearing position daySide
clrMark	DOUBLE		0	clearing mark daySide
comment	TINYTEXT		''
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	upload timestamp will be identical per accnt

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
secKey_tk	2
secKey_yr	3
secKey_mn	4
secKey_dy	5
secKey_xx	6
secKey_cp	7
secKey_at	8
secKey_ts	9
secType	10
tradeDate	11
clientFirm	12

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgSodClearingRecordV5` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0,
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'effective tradeDate of this clearing record',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm',
    `daySide` ENUM('StartOfDay','EndOfDay') NOT NULL DEFAULT 'StartOfDay' COMMENT 'start-of-day or prev weekday''s end-of-day; i.e. corpaction adjusted or not',
    `clrPosition` INT NOT NULL DEFAULT 0 COMMENT 'clearing position @ daySide',
    `clrMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'clearing mark @ daySide',
    `comment` TINYTEXT NOT NULL DEFAULT '',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'upload timestamp; will be identical per accnt',
    PRIMARY KEY USING HASH (`accnt`,`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`,`tradeDate`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SodClearingRecords contain start-of-day option position and mark details.  They can be source directly from a prime broker by SpiderRock or inserted by clients.\nThey can also be modified after the start of trading. These records are the source of the CLR side positions in SR risk records.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `tradeDate`,
    `clientFirm`,
    `daySide`,
    `clrPosition`,
    `clrMark`,
    `comment`,
    `timestamp`
FROM `SRRisk`.`MsgSodClearingRecordV5`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRRisk`.`MsgSodClearingRecordV5` 
SET
    /* Replace with a ENUM('StartOfDay','EndOfDay') */ 
    `daySide` = 'StartOfDay',
    /* Replace with a INT */ 
    `clrPosition` = 5,
    /* Replace with a DOUBLE */ 
    `clrMark` = 4.56,
    /* Replace with a TINYTEXT */ 
    `comment` = 'dummy tiny text',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
WHERE
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRRisk`.`MsgSodClearingRecordV5`(
    /* Replace with a VARCHAR(16) */ 
    `accnt`,
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts`,
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk`,
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr`,
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn`,
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy`,
    /* Replace with a DOUBLE */ 
    `secKey_xx`,
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp`,
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType`,
    /* Replace with a DATE */
    `tradeDate`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a ENUM('StartOfDay','EndOfDay') */ 
    `daySide`,
    /* Replace with a INT */ 
    `clrPosition`,
    /* Replace with a DOUBLE */ 
    `clrMark`,
    /* Replace with a TINYTEXT */ 
    `comment`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'Example_accnt',
    'None',
    'None',
    'Example_secKey_tk',
    123,
    1,
    1,
    4.56,
    'Call',
    'None',
    '2022-01-01',
    'Example_clientFirm',
    'StartOfDay',
    5,
    4.56,
    'dummy tiny text',
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRRisk`.`MsgSodClearingRecordV5` 
WHERE
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='SodClearingRecordV5' ORDER BY ordinal_position ASC;

Option SOD Insert Query

REPLACE INTO srrisk.msgsodclearingrecordv5 
(accnt,seckey_at,seckey_ts,seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,tradedate,clientfirm,dayside,clrposition,clrmark,TIMESTAMP)
VALUES ('T.CB','EQT','NMS','NKE',2025,1,17,70,'Call','Option','2024-08-09','SR','Startofday',150,5.85,NOW());

Stock SOD Insert Query

REPLACE INTO srrisk.msgsodclearingrecordv5 (accnt,seckey_at,seckey_ts,seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,tradedate,clientfirm,dayside,clrposition,clrmark,TIMESTAMP)
VALUES ('T.CB','EQT','NMS','JNJ',2000,0,0,0,'Call','Stock','2024-08-09','SR','Startofday',1000,145,NOW());

Future SOD Insert Query

REPLACE INTO srrisk.msgsodclearingrecordv5 (accnt,seckey_at,seckey_ts,seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,tradedate,clientfirm,dayside,clrposition,clrmark,TIMESTAMP)
VALUES ('T.CB','FUT','CME','ES',2024,9,20,0,'Call','Future','2024-08-09','SR','Startofday',850,5340,NOW());